﻿/*
 
    2018.11.13 修改获取错误主键的bug 
 */
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data.SqlClient;

namespace ZhCun.CodeBuilder.Builders
{
    class SqlServerBuilder : BaseBuilder
    {
        public SqlServerBuilder(string connStr)
            : base(connStr)
        { }
        protected internal override DbConnection CreateConnection()
        {
            return new SqlConnection(base.ConnectString);
        }
        protected override DbCommand CreateDbCommand()
        {
            return new SqlCommand();
        }
        protected override Dictionary<string, string> GetColumnRemarks(string tbName)
        {
            Dictionary<string, string> colRemarks = new Dictionary<string, string>();
            using (DbConnection conn = CreateConnection())
            {
                using (DbCommand cmd = new SqlCommand())
                {
                    StringBuilder sql = new StringBuilder();
                    sql.AppendFormat(@"
SELECT
	d.[name] as TableName,
	a.[colorder] as ColOrder,
	a.[name] as ColName,
	g.[value] AS ColRemark
FROM syscolumns a 
	inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
	left join sys.extended_properties g on a.id=g.major_id AND a.colid = g.minor_id 
WHERE d.[name] ='{0}'
", tbName);                    
                    cmd.Connection = conn;
                    cmd.CommandText = sql.ToString();
                    conn.Open();
                    using (DbDataReader dbReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
                    {
                        if (dbReader.HasRows)
                        {
                            while (dbReader.Read())
                            {
                                object colName = dbReader["ColName"];
                                object colRemark = dbReader["ColRemark"];
                                if (colName != null && colName != DBNull.Value && colName.ToString().Trim().Length > 0 &&
                                    colRemark != null && colRemark != DBNull.Value && colRemark.ToString().Trim().Length > 0)
                                {
                                    colRemarks.Add(colName.ToString(), colRemark.ToString());
                                }
                            }
                        }
                    }
                }
            }
            return colRemarks;
        }
        protected override List<string> GetPrimarykeys(string tableName)
        {
            List<string> pkList = new List<string>();
            using (DbConnection conn = CreateConnection())
            {
                using (DbCommand cmd = new SqlCommand())
                {
                    StringBuilder sql = new StringBuilder();

                    // 2018.11.13 修改获取主键错误的bug
                    sql.AppendFormat(@"
SELECT SYSCOLUMNS.name as ColumnName
FROM SYSCOLUMNS,SYSOBJECTS,SYSINDEXES,SYSINDEXKEYS 
WHERE SYSCOLUMNS.id = object_id('{0}') AND SYSOBJECTS.xtype = 'PK' 
AND SYSOBJECTS.parent_obj = SYSCOLUMNS.id 
AND SYSINDEXES.id = SYSCOLUMNS.id AND SYSOBJECTS.name = SYSINDEXES.name 
AND SYSINDEXKEYS.id = SYSCOLUMNS.id AND SYSINDEXKEYS.indid = SYSINDEXES.indid 
AND SYSCOLUMNS.colid = SYSINDEXKEYS.colid", tableName);
                    cmd.Connection = conn;
                    cmd.CommandText = sql.ToString();
                    conn.Open();
                    using (DbDataReader dbReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
                    {
                        if (dbReader.HasRows)
                        {
                            while (dbReader.Read())
                            {
                                object colName = dbReader["ColumnName"];
                                if (colName != null && colName != DBNull.Value && colName.ToString().Trim().Length > 0)
                                {
                                    pkList.Add(colName.ToString());
                                }
                            }
                        }
                    }
                }
            }
            return pkList;
        }

        //protected override string GetCSharpDataTypeString(string dbDataType)
        //{
        //    string r;
        //    switch (dbDataType.ToLower())
        //    {
        //        case "tinyint":
        //        case "int":
        //        case "integer":
        //            r = "int";
        //            break;
        //        case "smallint":
        //            r = "uint";
        //            break;
        //        case "bigint":
        //            r = "long";
        //            break;
        //        case "boolean":
        //        case "bit":
        //            r = "bool";
        //            break;
        //        case "uniqueidentifier":
        //            r = "Guid";
        //            break;
        //        case "smalldatetime":
        //        case "datetime":
        //        case "date":
        //            r = "DateTime";
        //            break;
        //        case "decimal":
        //        case "money":
        //        case "numeric":
        //        case "smallmoney":
        //            r = "decimal";
        //            break;
        //        case "float":
        //        case "real":
        //            r = "double";
        //            break;
        //        case "varchar":
        //        case "nvarchar":
        //        case "char":
        //        case "text":
        //        case "":
        //            r = "string";
        //            break;
        //        default:  //未知的数据类型都是string
        //            r = "object";
        //            break;
        //    }
        //    return r;
        //}
    }
}